package com.prodavalnik.model;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.prodavalnik.exceptions.CategoryExistedException;
import com.prodavalnik.exceptions.UserAlreadyExistOnDBException;


public class DataBaseController {
	private static final String SELECT_CATEGORY_ID_BY_PRODUCTS_ID = "SELECT CATEGORY_ID FROM PRODUCTS WHERE PRODUCT_ID = ?";
	private static final String SELECT_USER_ID_FROM_USERNAME = "SELECT USER_ID FROM USERS WHERE USERNAME = ?";
	private static final String SELECT_ALL_SUBCATEORIES_OF_CATEGORY = "SELECT C.CATEGORY_ID, C.CATEGORY_NAME,C .CATEGORY_UP FROM CATEGORIES C1 "+
			" JOIN CATEGORIES C ON(C.CATEGORY_UP = C1.CATEGORY_ID) "+
			" WHERE C1.CATEGORY_NAME = ? ";
	private static final String SELECT_ALL_ROOT_CATEGORIES = "SELECT CATEGORY_ID, CATEGORY_NAME FROM CATEGORIES WHERE CATEGORY_UP IS NULL";
	private static final String SELECT_USER_LIKED_CATEGORIES_BY_USER_ID = "SELECT * FROM USER_LIKED_CATEGORIES WHERE USER_ID = ?";
	private static final String SELECT_USER_BOUGHT_BY_USER_ID = "SELECT * FROM USER_BOUGHT WHERE USER_ID = ?";
	private static final String SELECT_USER_SALES_BY_USER_ID = "SELECT * FROM USER_SALES WHERE USER_ID = ?";
	private static final String UPDATE_USE_SQL_QUERY = "UPDATE USERS SET USER_EMAIL =?,USER_CONTACTS =? WHERE USERNAME =?";
	private static final String SELECT_FROM_USER_BOUGHT = "SELECT* FROM USER_BOUGHT WHERE (USER_ID = ? AND PRODUCT_ID = ?)";
	private static final String SELECT_FROM_USER_LIKED_CATEGORIES = "SELECT* FROM USER_LIKED_CATEGORIES WHERE (USER_ID = ? AND CATEGORY_ID = ?)";
	private static final String SELECT_FROM_USER_SALES = "SELECT* FROM USER_SALES WHERE (USER_ID = ? AND PRODUCT_ID = ?)";
	private static final String INSERT_INTO_USER_LIKED_CATEGORIES_USER_ID = "INSERT INTO USER_LIKED_CATEGORIES (USER_ID,CATEGORY_ID) VALUES(?,?)";
	private static final String INSERT_INTO_USER_BOUGHT_USER_ID = "INSERT INTO USER_BOUGHT (USER_ID,PRODUCT_ID) VALUES(?,?)";
	private static final String INSERT_INTO_USER_SALES_USER_ID = "INSERT INTO USER_SALES (USER_ID,PRODUCT_ID) VALUES(?,?)";
	private static final String INSERT_PRODUCT = "INSERT INTO PRODUCTS(PRODUCT_IMG,PRODUCT_NAME,PRODUCT_DESCRIPTION,PRODUCT_PRICE,PRODUCT_TIMESTAMP,CATEGORY_ID) VALUES(?,?,?,?,?,?)";
	private static final String INSERT_USER_SQL_QUERY = "INSERT INTO USERS(USERNAME,USER_PASSWORD,USER_EMAIL,USER_CONTACTS) VALUES(?,?,?,?)";
	private static final String SELECT_THE_LOWEST_SUBCATEGORIES_OF_CATEGORY="SELECT ID_CAT,CATEGORY_NAME FROM(SELECT CATEGORY_ID AS ID_CAT,category_name FROM CATEGORIES "+
																			"START WITH CATEGORY_NAME = ? "+
																			"CONNECT BY PRIOR CATEGORY_ID = CATEGORY_UP) "+
																			"WHERE NOT EXISTS(SELECT* FROM CATEGORIES CAT WHERE CAT.CATEGORY_UP = ID_CAT)";
	private static final String ORACLE_USER = "JP";
	private static final String ORACLE_USER_PASS = "JP";
	private static final String ORACLE_DB_URL = "jdbc:oracle:thin:@localhost:1521/xe";
	private static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	public static final String PATH_TO_DIR_WHERE_IMAGES_ARE_SAVED="D:\\Java\\prodavalnik-project\\Prodavalnik\\WebContent\\img";
	
	/**
	 * 
	 * @getConnectioFromDb
	 * 	return Object from type Connection if the connection to DB is successfull,
	 * 	null otherwise
	 * 
	 * */
	public static Connection getConnectioFromDB() {
		try {
			Class.forName(ORACLE_DRIVER);
			Connection dbConnection = DriverManager.getConnection(ORACLE_DB_URL,
										ORACLE_USER,ORACLE_USER_PASS);
			return dbConnection;
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	/**
	 * 
	 * @param username
	 * @param password
	 * @param email
	 * @param conn
	 * @throws SQLException
	 * @throws UserAlreadyExistOnDBException if user exist on DataBase
	 * 
	 */
	public static void addUser(String username,String password, String email,String conn) throws SQLException,UserAlreadyExistOnDBException{
		
		Connection dbCon = getConnectioFromDB();
		if(returnUserByUsernameAndPassword(username, password)!=null){
			throw new UserAlreadyExistOnDBException();
		}
			Statement stmt = dbCon.createStatement();
			PreparedStatement statement = dbCon.prepareStatement(INSERT_USER_SQL_QUERY);
			statement.setString(1, username);
			statement.setString(2, password);
			statement.setString(3, email);
			statement.setString(4, conn);
			statement.executeUpdate();
			if(stmt!=null)
				stmt.close();
			if(dbCon != null)
					dbCon.close();
	}
	public static void changeUser(String username,String password,String email,String conn) throws SQLException{
		
		Connection dbCon = getConnectioFromDB();
		Statement stmt = dbCon.createStatement();
		PreparedStatement statement = dbCon.prepareStatement(UPDATE_USE_SQL_QUERY);
		statement.setString(1,email);
		statement.setString(2, conn);
		statement.setString(3, username);
		statement.executeUpdate();
		if(stmt != null)
			stmt.close();
		if(dbCon != null)
			dbCon.close();
	}
	/**
	 * 
	 * @param username
	 * @param password
	 * @return Object of type User if the user is in DB or null otherwise 
	 * @throws SQLException
	 */
	public static User returnUserByUsernameAndPassword(String username,String password) throws SQLException{
		Connection dbCon = getConnectioFromDB();
		Statement stmt = dbCon.createStatement();
		PreparedStatement statement = dbCon.prepareStatement("SELECT* FROM USERS WHERE (username = ? AND user_password = ?) ");
		statement.setString(1, username);
		statement.setString(2, password);
		ResultSet rsUsers = statement.executeQuery();
		while(rsUsers.next()){
			return new User(rsUsers.getInt("USER_ID"),rsUsers.getString("USERNAME"),rsUsers.getString("USER_PASSWORD"),
							rsUsers.getString("USER_EMAIL"),rsUsers.getString("USER_CONTACTS"));
			
		}
		if(stmt!=null)
			stmt.close();
		if(dbCon != null)
				dbCon.close();
		return null;
	}
	
	/**
	 * 
	 * @param dbCon Connection
	 * @param name Name of category
	 * @return null if category does't exist or object of type Category if exists
	 * @throws SQLException 
	 */
	private static Category findCategory(Connection dbCon,String name) throws SQLException{
		if(dbCon == null)
			return null;
		Statement stmt = dbCon.createStatement();
		PreparedStatement statement = dbCon.prepareStatement("SELECT* FROM CATEGORIES WHERE CATEGORY_NAME = ?");
		statement.setString(1, name);
		ResultSet rsCategory = statement.executeQuery();
		Category category=null;
		while(rsCategory.next()){
			category = new Category(rsCategory.getInt("CATEGORY_ID"),rsCategory.getString("CATEGORY_NAME"),rsCategory.getInt("CATEGORY_UP"));
		}
		if(stmt!=null)
			stmt.close();
		
		return category;
		
	}
	public static Category findCategoryByName(String name){
		Connection dbCon=getConnectioFromDB();
		try {
			Category c = null;
			c= findCategory(dbCon, name);
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(dbCon!=null)
				try {
					dbCon.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return null;
	}
	
	public static Category addCategory(Connection dbCon,String name,int category_up) throws SQLException{
		if(dbCon == null)
			return null;
		PreparedStatement statement = null;
		if(findCategory(dbCon, name)==null){
			if(category_up != 0){	
				statement = dbCon.prepareStatement("INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES(?,?)");
				statement.setString(1, name);
				statement.setInt(2, category_up);
			}
			else{
				statement = dbCon.prepareStatement("INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES(?,NULL)");
				statement.setString(1, name);
			}
		}
		else{
			
			statement = dbCon.prepareStatement("UPDATE CATEGORIES SET CATEGORY_UP = ? WHERE CATEGORY_NAME = ?");
			statement.setString(2, name);
			statement.setInt(1, category_up);
		}
		statement.executeUpdate();
		return null;
		
	}
	/**
	 * 
	 * @param categoryPath the path to Category example Smarthphone/GSM/MobileDevices 
	 * @throws SQLException
	 * @throws CategoryExistedException if the category exists 
	 */
	
	public static void addCategoryPath(String categoryPath) throws SQLException, CategoryExistedException{
		String[] subCategory = categoryPath.split("/");
		Connection dbCon = getConnectioFromDB();
		if(findCategory(dbCon, subCategory[0])!=null)
			throw new CategoryExistedException();
		for(int i = 0; i < subCategory.length - 1; i++){
			
			
			int nextUpperCategory = 0;
			Category tempCategory = findCategory(dbCon, subCategory[i+1]);
			if(tempCategory == null){
				addCategory(dbCon, subCategory[i+1],0);
				tempCategory = findCategory(dbCon, subCategory[i+1]);
				
			}
			nextUpperCategory = tempCategory.getId();
			addCategory(dbCon, subCategory[i],nextUpperCategory);
		}
		
	
		if(dbCon !=null)
			dbCon.close();
	}
	
	public static void addProductToDB(Product pd){
		ByteArrayOutputStream imagesOutputStream = new ByteArrayOutputStream();
		ByteArrayInputStream imagesInputStream=null;
		ObjectOutputStream oos = null;
		PreparedStatement insertProduct=null;
		Connection dbCon=null;
		
		try {
			oos = new ObjectOutputStream(imagesOutputStream);
			oos.writeObject(pd.getImages());
			byte[] imagesAsBytes = imagesOutputStream.toByteArray();
			imagesInputStream = new ByteArrayInputStream(imagesAsBytes);
			dbCon = getConnectioFromDB();
			insertProduct = dbCon.prepareStatement(INSERT_PRODUCT);
			insertProduct.setString(2, pd.getName());
		
			insertProduct.setString(3, pd.getDescription());
			insertProduct.setBigDecimal(4, pd.getPrice());
		
			insertProduct.setDate(5, new java.sql.Date(pd.getTimeStamp().getTime()));
			insertProduct.setInt(6,pd.getCategory());
			insertProduct.setBinaryStream(1, imagesInputStream,imagesAsBytes.length);
			insertProduct.executeUpdate();
		
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
		if(imagesOutputStream != null)
			try {
				imagesOutputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		if(imagesInputStream != null)
			try {
				imagesInputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if(oos!=null)
			try {
				oos.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if(insertProduct!=null)
			try {
				insertProduct.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		if(dbCon!=null)
			try {
				dbCon.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static ArrayList<Product> getProductFromDB(int id){
		ArrayList<Product> product = new ArrayList<Product>();
		Connection dbCon=null;
		PreparedStatement getProduct=null;
		dbCon = getConnectioFromDB();
		try {
			getProduct = dbCon.prepareStatement("SELECT* FROM PRODUCTS WHERE CATEGORY_ID= ? ");
			getProduct.setInt(1, id);
			ResultSet rs=getProduct.executeQuery();
			List<File> prImages=null;
			
			while(rs.next()){
				
				InputStream is = rs.getBinaryStream(2);
				if(is!=null){
					//ByteArrayInputStream baip = new ByteArrayInputStream(images);
					ObjectInputStream ois = new ObjectInputStream(is);
					prImages = (List<File>)ois.readObject();
					if(ois!=null)
						ois.close();
				}
				Product p = new Product(rs.getInt(7),rs.getString(3),rs.getString(4),rs.getBigDecimal(5),
										(java.util.Date)rs.getDate(6),prImages);
				p.setId(rs.getInt(1));
				product.add(p);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(dbCon!=null)
				try {
					dbCon.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return product;
		
		
	}
	
	public static ArrayList<Product> getCertainProductFromDB(int id){
		ArrayList<Product> product = new ArrayList<Product>();
		Connection dbCon=null;
		PreparedStatement getProduct=null;
		dbCon = getConnectioFromDB();
		try {
			getProduct = dbCon.prepareStatement("SELECT* FROM PRODUCTS WHERE PRODUCT_ID= ? ");
			getProduct.setInt(1, id);
			ResultSet rs=getProduct.executeQuery();
			List<File> prImages=null;
			
			while(rs.next()){
				
				InputStream is = rs.getBinaryStream(2);
				if(is!=null){
					ObjectInputStream ois = new ObjectInputStream(is);
					prImages = (List<File>)ois.readObject();
					if(ois!=null)
						ois.close();
				}
				Product p = new Product(rs.getInt(7),rs.getString(3),rs.getString(4),rs.getBigDecimal(5),
										(java.util.Date)rs.getDate(6),prImages);
				p.setId(rs.getInt(1));
				product.add(p);
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		finally{
			if(dbCon!=null)
				try {
					dbCon.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return product;
		
	}
	private static boolean hasAColumnWithParamAs(Connection dbCon, int userId,int productId,String query){
		PreparedStatement addColumnInUserSales = null;
		String selectQuery = null;
		switch(query){
		case INSERT_INTO_USER_BOUGHT_USER_ID:selectQuery = SELECT_FROM_USER_BOUGHT;break;
		case INSERT_INTO_USER_LIKED_CATEGORIES_USER_ID:selectQuery = SELECT_FROM_USER_LIKED_CATEGORIES;break;
		case INSERT_INTO_USER_SALES_USER_ID:selectQuery = SELECT_FROM_USER_SALES;break;
		}
		try {
			addColumnInUserSales = dbCon.prepareStatement(selectQuery);
			addColumnInUserSales.setInt(1, userId);
			addColumnInUserSales.setInt(2, productId);
			ResultSet rs = addColumnInUserSales.executeQuery();
			while(rs.next()){
				addColumnInUserSales.close();
				rs.close();
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(addColumnInUserSales!=null){
				try {
					addColumnInUserSales.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		return false;
	}
	private static void mapUserIdToCategoryOrProductId(int userId,int productOrCategoryId,String query){
		Connection dbCon = getConnectioFromDB();
		PreparedStatement addColumnInUserSales = null;
		try {
			if(hasAColumnWithParamAs(dbCon, userId, productOrCategoryId,query))
			{
				if(addColumnInUserSales != null)
					addColumnInUserSales.close();
				dbCon.close();
				return ;
			}
			addColumnInUserSales = dbCon.prepareStatement(query);
			addColumnInUserSales.setInt(1, userId);
			addColumnInUserSales.setInt(2, productOrCategoryId);
			addColumnInUserSales.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(dbCon!=null)
				try {
					dbCon.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if(addColumnInUserSales!=null){
				try {
					addColumnInUserSales.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void addRecordToUserSales(int userId,int productId){
		mapUserIdToCategoryOrProductId(userId, productId,INSERT_INTO_USER_SALES_USER_ID);
	}
	
	public static void addRecordToUserLikedCategories(int userId,int categoryId){
		mapUserIdToCategoryOrProductId(userId, categoryId,INSERT_INTO_USER_LIKED_CATEGORIES_USER_ID);
	}
	
	public static void addRecordToUserBought(int userId,int productId){
		mapUserIdToCategoryOrProductId(userId, productId,INSERT_INTO_USER_BOUGHT_USER_ID);
	}
	
	public static int getRecordForUserSales(int userId) throws SQLException{
		Connection dbCon=null;
		PreparedStatement getRecord=null;
		dbCon = getConnectioFromDB();
		int count=0;
			getRecord = dbCon.prepareStatement(SELECT_USER_SALES_BY_USER_ID);
			getRecord.setInt(1, userId);
			ResultSet rsUsers = getRecord.executeQuery();
			while(rsUsers.next()){
				count++;}
			if(getRecord != null){
					getRecord.close();
			}
			if(dbCon != null){
				dbCon.close();
			}
			return count;
		}
	
	public static int getRecordForUserBought(int userId) throws SQLException{
		Connection dbCon=null;
		PreparedStatement getRecord=null;
		dbCon = getConnectioFromDB();
		int count=0;
			getRecord = dbCon.prepareStatement(SELECT_USER_BOUGHT_BY_USER_ID);
			getRecord.setInt(1, userId);
			ResultSet rsUsers = getRecord.executeQuery();
			while(rsUsers.next()){
				count++;}
			if(getRecord != null){
					getRecord.close();
			}
			if(dbCon != null){
				dbCon.close();
			}
			return count;
		}
	
	public static int getRecordForUserLikedCategories(int userId) throws SQLException{
		Connection dbCon=null;
		PreparedStatement getRecord=null;
		dbCon = getConnectioFromDB();
		int count=0;
			getRecord = dbCon.prepareStatement(SELECT_USER_LIKED_CATEGORIES_BY_USER_ID);
			getRecord.setInt(1, userId);
			ResultSet rsUsers = getRecord.executeQuery();
			while(rsUsers.next()){
				count++;}
			if(getRecord != null){
					getRecord.close();
			}
			if(dbCon != null){
				dbCon.close();
			}
			return count;
		}

	
	public static List<Category> getAllRootCategories(){
		return getCategoriesWithQuery(null,SELECT_ALL_ROOT_CATEGORIES);
	}
	
	public static List<Category> getCategoriesByName(String name){
		return getCategoriesWithQuery(name,SELECT_ALL_SUBCATEORIES_OF_CATEGORY);
	}
	
	public static List<Category> getTheLowestSubcategoiresOfCategory(String name){
		return getCategoriesWithQuery(name,SELECT_THE_LOWEST_SUBCATEGORIES_OF_CATEGORY);
		
	}
	private static List<Category> getCategoriesWithQuery(String name,String query) {
		Connection dbCon=null;
		PreparedStatement lowestCategories=null;
		dbCon = getConnectioFromDB();
		try {
			lowestCategories = dbCon.prepareStatement(query);
			if(name!=null)
				lowestCategories.setString(1, name);
			ResultSet rs=lowestCategories.executeQuery();
			List<Category> rootCategories=new ArrayList<Category>();
			
			while(rs.next()){
				int id = rs.getInt(1);
				String tempString = rs.getString(2);
				rootCategories.add(new Category(id,tempString,0));
			
			}
			if(rs!=null)
				rs.close();
			if(lowestCategories!=null)
				lowestCategories.close();
			return rootCategories;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(dbCon!=null)
				try {
					dbCon.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return null;
	}
	
	public static int getUserId(String username) throws SQLException{
		Connection dbCon=null;
		PreparedStatement getRecord=null;
		dbCon = getConnectioFromDB();
			getRecord = dbCon.prepareStatement(SELECT_USER_ID_FROM_USERNAME);
			getRecord.setString(1, username);
			ResultSet rsUsers = getRecord.executeQuery();
			int result = 0;
			while(rsUsers.next()){
				result = rsUsers.getInt(1);
			
			}
			
			if(rsUsers!=null){
				rsUsers.close();
			}
			if(getRecord != null){
					getRecord.close();
			}
			if(dbCon != null){
				dbCon.close();
			}
			return result;
	}
	public static int getCategoryIdByProductId(int productID) throws SQLException{
		Connection dbCon=null;
		PreparedStatement getRecord=null;
		dbCon = getConnectioFromDB();
			getRecord = dbCon.prepareStatement(SELECT_CATEGORY_ID_BY_PRODUCTS_ID);
			getRecord.setInt(1, productID);
			ResultSet rsUsers = getRecord.executeQuery();
			int result = 0;
			while(rsUsers.next()){
				result = rsUsers.getInt(1);
			}
			
			if(rsUsers != null){
				rsUsers.close();
			}
			if(getRecord != null){
					getRecord.close();
			}
			if(dbCon != null){
				dbCon.close();
			}
			return result;
	}
	public static boolean search(String search , Product p){
				
		String name = p.getName().toUpperCase();
		String desc = p.getDescription().toUpperCase();
		
		if(name.contains(search) || desc.contains(search)){
			return true;
		}
		return false;
		
	}

}
